package com.csun.dsms.controller;

import cn.hutool.core.date.DatePattern;
import com.alibaba.fastjson.JSONObject;
import com.csun.dsms.comment.core.annotation.Authorization;
import com.csun.dsms.comment.core.utils.wrapper.WrapMapper;
import com.csun.dsms.comment.core.utils.wrapper.Wrapper;
import com.csun.dsms.model.huang.*;
import com.csun.dsms.model.vo.ihg.iHgDown;
import com.csun.dsms.service.IHgUserService;
import com.csun.dsms.service.IhgDataProcessService;
import com.csun.dsms.utils.DateUtil;
import com.csun.dsms.utils.ExportExcel;
import com.csun.dsms.utils.pt.HuangGangUtil;
import com.csun.dsms.utils.pt.IHuangGangUtil;
import com.github.pagehelper.PageInfo;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.util.ObjectUtils;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.util.List;

/**
 * @desc   i黄冈数据处理
 */
@Slf4j
@RestController
@RequestMapping("/ihgDataProcess")
public class IhgDataProcessController {

    @Autowired
    private IhgDataProcessService ihgDataProcessService;

    @Autowired
    private IHgUserService iHgUserService;

    @Authorization
    @RequestMapping(value = "/getQrCode",method = RequestMethod.POST)
    public Wrapper getQrCode() {
        String result = IHuangGangUtil.login();
        return WrapMapper.ok(result);
    }

    /**
     * i黄冈登录校验 扫码
     * 用户登录调取比对数据库是否存在（手机号和身份证号码），存在即可登录，返回user表，不存在抛异常
     */
    @Authorization
    @RequestMapping(value = "/loginCheck",method = RequestMethod.POST)
    public Wrapper loginCheck(@RequestBody String uuid) {
        JSONObject aesJsonBean = JSONObject.parseObject(uuid);
        String UID = aesJsonBean.getString("uuid");
        String data = IHuangGangUtil.getCodeResult(UID);
        if (ObjectUtils.isEmpty(data)){
            return WrapMapper.error("禁止登录");
        }else{
            JSONObject jsonObject = JSONObject.parseObject(data);
            if (jsonObject.getIntValue("code") == 1){
                String dataStr = jsonObject.getString("data");
                System.out.println(dataStr);
                JSONObject dataJson = JSONObject.parseObject(dataStr);
                String userType = dataJson.getString("userType");
                System.out.println(userType);
                String userPhone = "";
                if (userType!=null && userType.equals("1")){
                    userPhone = dataJson.getString("mobilePhone");
                }else if (userType!=null && userType.equals("2")){
                    userPhone = dataJson.getString("agentMobile");
                }
                IhgUser user = ihgDataProcessService.loginByPhone(userPhone);

                if (ObjectUtils.isEmpty(user)){
                    return WrapMapper.error("禁止登录");
                }else{
                    return WrapMapper.ok(user);
                }
            }
            return WrapMapper.error("禁止登录");
        }
    }


    @Authorization
    @RequestMapping(value = "/loginByPhone",method = RequestMethod.POST)
    public Wrapper loginByPhone(@RequestBody String phone) {
        JSONObject aesJsonBean = JSONObject.parseObject(phone);
        String mobilePhone = aesJsonBean.getString("phone");
        IhgUser user = ihgDataProcessService.loginByPhone(mobilePhone);
        if (ObjectUtils.isEmpty(user)){
            return WrapMapper.error("禁止登录");
        }else{
            return WrapMapper.ok(user);
        }
    }


    @Authorization
    @RequestMapping(value = "/getUserList",method = RequestMethod.POST)
    public Wrapper getUserList(@RequestBody IHgQueryDto iHgQueryDto){
        List userList = iHgUserService.queryList(iHgQueryDto);
        return WrapMapper.ok(userList);
    }

    @Authorization
    @RequestMapping(value = "/editUser",method = RequestMethod.POST)
    public Wrapper editUser (@RequestBody IhgUser hgUser){
        Integer result = iHgUserService.update(hgUser);
        return WrapMapper.wrap(Wrapper.SUCCESS_CODE,Wrapper.SUCCESS_MESSAGE,result);
    }

    @Authorization
    @RequestMapping(value = "/addUser",method = RequestMethod.POST)
    public Wrapper addUser (@RequestBody IhgUser hgUser){
        Integer result = iHgUserService.save(hgUser);
        return WrapMapper.wrap(Wrapper.SUCCESS_CODE,Wrapper.SUCCESS_MESSAGE,result);
    }


    @Authorization
    @PostMapping(value = "/deleteUserById/{id}")
    public Wrapper deleteById (@PathVariable Long id){
        Integer result = iHgUserService.deleteByKey(id);
        return WrapMapper.wrap(Wrapper.SUCCESS_CODE,Wrapper.SUCCESS_MESSAGE,result);
    }

    /*
     * 查询政府服务数据
     * */
    @Authorization
    @RequestMapping(value = "/queryGovernmentData",method = RequestMethod.POST)
    public Wrapper queryGovernmentData (@RequestBody PageParameter<IhgGovernmentServiceBean> ihgGovernmentServiceBeanPage){
        return ihgDataProcessService.queryGovernmentData(ihgGovernmentServiceBeanPage);
    }

    @Authorization
    @RequestMapping(value = "/queryGovernmentPage",method = RequestMethod.POST)
    public Wrapper queryGovernmentPage (@RequestBody IHgQueryDto iHgQueryDto){
        PageInfo page = ihgDataProcessService.queryGovernmentPage(iHgQueryDto);
        return WrapMapper.wrap(Wrapper.SUCCESS_CODE,Wrapper.SUCCESS_MESSAGE,page);
    }

    @Authorization
    @RequestMapping(value = "/querySocialPage",method = RequestMethod.POST)
    public Wrapper querySocialPage (@RequestBody IHgQueryDto iHgQueryDto){
        PageInfo page = ihgDataProcessService.querySocialPage(iHgQueryDto);
        return WrapMapper.wrap(Wrapper.SUCCESS_CODE,Wrapper.SUCCESS_MESSAGE,page);
    }


    @Authorization
    @RequestMapping(value = "/querySocialLastDay",method = RequestMethod.POST)
    public Wrapper querySocialLastDay (@RequestBody IHgQueryDto iHgQueryDto){
        PageInfo page = ihgDataProcessService.querySocialLastDay(iHgQueryDto);
        return WrapMapper.wrap(Wrapper.SUCCESS_CODE,Wrapper.SUCCESS_MESSAGE,page);
    }

    @Authorization
    @RequestMapping(value = "/queryGovLastDay",method = RequestMethod.POST)
    public Wrapper queryGovLastDay (@RequestBody IHgQueryDto iHgQueryDto){
        PageInfo page = ihgDataProcessService.queryGovLastDay(iHgQueryDto);
        return WrapMapper.wrap(Wrapper.SUCCESS_CODE,Wrapper.SUCCESS_MESSAGE,page);
    }

    /**
     * 新增政府服务数据
     */
    @Authorization
    @RequestMapping(value = "/addGovernmentData",method = RequestMethod.POST)
    public Wrapper addGovernmentData (@RequestBody IhgGovernmentServiceBean ihgGovernmentServiceBean){
        return ihgDataProcessService.addGovernmentData(ihgGovernmentServiceBean);
    }

    /**
     * 修改政府服务数据
     */
    @Authorization
    @RequestMapping(value = "/updateGovernmentData",method = RequestMethod.POST)
    public Wrapper updateGovernmentData (@RequestBody IhgGovernmentServiceBean ihgGovernmentServiceBean) {
        return ihgDataProcessService.updateGovernmentData(ihgGovernmentServiceBean);
    }

    /**
     * 删除政府服务数据
     */
    @Authorization
    @RequestMapping(value = "/deleteGovernmentData",method = RequestMethod.POST)
    public Wrapper deleteGovernmentData (@RequestBody IhgGovernmentServiceBean ihgGovernmentServiceBean) {
        return ihgDataProcessService.deleteGovernmentData(ihgGovernmentServiceBean);
    }

    /*
     * 查询社会服务数据
     * */
    @Authorization
    @RequestMapping(value = "/querySocialData",method = RequestMethod.POST)
    public Wrapper querySocialData (@RequestBody PageParameter<IhgSocialServiceBean> ihgSocialServiceBeanPage){
        return ihgDataProcessService.querySocialData(ihgSocialServiceBeanPage);
    }

    /**
     * 新增社会服务数据
     */
    @Authorization
    @RequestMapping(value = "/addSocialData",method = RequestMethod.POST)
    public Wrapper addSocialData (@RequestBody IhgSocialServiceBean ihgSocialServiceBean){
        return ihgDataProcessService.addSocialData(ihgSocialServiceBean);
    }

    /**
     * 修改社会服务数据
     */
    @Authorization
    @RequestMapping(value = "/updateSocialData",method = RequestMethod.POST)
    public Wrapper updateSocialData (@RequestBody IhgSocialServiceBean ihgSocialServiceBean) {
        return ihgDataProcessService.updateSocialData(ihgSocialServiceBean);
    }

    /**
     * 删除社会服务数据
     */
    @Authorization
    @RequestMapping(value = "/deleteSocialData",method = RequestMethod.POST)
    public Wrapper deleteSocialData (@RequestBody IhgSocialServiceBean ihgSocialServiceBean) {
        return ihgDataProcessService.deleteSocialData(ihgSocialServiceBean);
    }

    /**
     * 根据部门,模块,项目统计 社区的
     */
    @Authorization
    @RequestMapping(value = "/querySocialStatistical",method = RequestMethod.POST)
    public Wrapper querySocialStatistical (@RequestBody JSONObject reqJson) {
        return ihgDataProcessService.querySocialStatistical(reqJson);
    }

    /**
     * 根据部门,模块,项目统计 政府的
     */
    @Authorization
    @RequestMapping(value = "/queryGovernmentStatistical",method = RequestMethod.POST)
    public Wrapper queryGovernmentStatistical (@RequestBody JSONObject reqJson) {
        return ihgDataProcessService.queryGovernmentStatistical(reqJson);
    }

    /**
     * 根据部门,模块,项目统计 政府的
     */
    @Authorization
    @RequestMapping(value = "/queryGovernmentDepartStatistical",method = RequestMethod.POST)
    public Wrapper queryGovernmentDepartStatistical (@RequestBody JSONObject reqJson) {
        return ihgDataProcessService.queryGovernmentDepartStatistical(reqJson);
    }

    /**
     * 查询统计数据
     */
    @Authorization
    @RequestMapping(value = "/queryStatistical",method = RequestMethod.POST)
    public Wrapper queryStatistical (@RequestBody JSONObject reqJson) {
        return ihgDataProcessService.queryStatistical(reqJson);
    }

    /**
     * 查询政府统计数据（根据服务项目名称）
     */
    @Authorization
    @RequestMapping(value = "/queryGovernmentStatisticalByProjectName",method = RequestMethod.POST)
    public Wrapper queryGovernmentStatisticalByProjectName (@RequestBody JSONObject reqJson) {
        return ihgDataProcessService.queryGovernmentStatisticalByProjectName(reqJson);
    }

    /**
     * 查询社会统计数据（根据服务项目名称）
     */
    @Authorization
    @RequestMapping(value = "/querySocialStatisticalByProjectName",method = RequestMethod.POST)
    public Wrapper querySocialStatisticalByProjectName (@RequestBody JSONObject reqJson) {
        return ihgDataProcessService.querySocialStatisticalByProjectName(reqJson);
    }

    @GetMapping("/downGovernmentDataTemplate")
    public void downGovernmentDataTemplate(HttpServletResponse response,IHgQueryDto iHgQueryDto) {
        HSSFWorkbook wb = new HSSFWorkbook();
        String worksheetTitle = "县市区政府服务数据导入模板表";
        // 创建单元格样式
        HSSFCellStyle cellStyleTitle = wb.createCellStyle();
        // 指定单元格居中对齐
        cellStyleTitle.setAlignment(HorizontalAlignment.CENTER);
        // 指定单元格垂直居中对齐
        cellStyleTitle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 指定当单元格内容显示不下时不换行
        cellStyleTitle.setWrapText(false);
        // ------------------------------------------------------------------
        HSSFCellStyle cellStyle = wb.createCellStyle();
        // 指定单元格居中对齐
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        // 指定单元格垂直居中对齐
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 指定当单元格内容显示不下时换行
        cellStyle.setWrapText(true);
        // 创建日期格式样式
        HSSFCellStyle dateCellStyle = wb.createCellStyle();
        dateCellStyle.cloneStyleFrom(cellStyle);
        short dateFormat = wb.createDataFormat().getFormat("yyyy年MM月dd日");
        dateCellStyle.setDataFormat(dateFormat);
        // ------------------------------------------------------------------
        // 设置单元格字体
        HSSFFont font = wb.createFont();
         font.setBold(true);
        font.setFontName("宋体");
        font.setFontHeight((short) 200);
        cellStyleTitle.setFont(font);

        // 工作表名
        String areaName = "区划地名";
        String department = "部门名称";
        String projectName = "服务项目名称";
        String remarkOne = "所有渠道办理的总办件数";
        String remarkTwo = "所有渠道办理涉及的总金额（万元）";
        String remarkThree = "i黄冈办理的办件数";
        String remarkFour = "i黄冈办理涉及的金额（万元）";
        String creator = "填报人";
        String createdTime = "填报时间";

        HSSFSheet sheet = wb.createSheet();
        ExportExcel exportExcel = new ExportExcel(wb, sheet);
        // 创建报表头部
        //exportExcel.createNormalHead(worksheetTitle, 11);
        // 定义第一行
        HSSFRow row1 = sheet.createRow(0);
        //第一行第1列
        HSSFCell cell1 = row1.createCell(0);
        cell1.setCellStyle(cellStyleTitle);
        cell1.setCellValue(new HSSFRichTextString(areaName));
        sheet.setColumnWidth(0, 20 * 256);;
        //第一行第2列
        cell1 = row1.createCell(1);
        cell1.setCellStyle(cellStyleTitle);
        cell1.setCellValue(new HSSFRichTextString(department));
        sheet.setColumnWidth(1, 20 * 256);
        //第一行第3列
        cell1 = row1.createCell(2);
        cell1.setCellStyle(cellStyleTitle);
        cell1.setCellValue(new HSSFRichTextString(projectName));
        sheet.setColumnWidth(2, 20 * 256);

        //第一行第5列
        cell1 = row1.createCell(3);
        cell1.setCellStyle(cellStyleTitle);
        cell1.setCellValue(new HSSFRichTextString(remarkOne));
        sheet.setColumnWidth(3, 20 * 256);
        //第一行第6列
        cell1 = row1.createCell(4);
        cell1.setCellStyle(cellStyleTitle);
        cell1.setCellValue(new HSSFRichTextString(remarkTwo));
        sheet.setColumnWidth(4, 20 * 256);
        //第一行第7列
        cell1 = row1.createCell(5);
        cell1.setCellStyle(cellStyleTitle);
        cell1.setCellValue(new HSSFRichTextString(remarkThree));
        sheet.setColumnWidth(5, 20 * 256);
        //第一行第7列
        cell1 = row1.createCell(6);
        cell1.setCellStyle(cellStyleTitle);
        cell1.setCellValue(new HSSFRichTextString(remarkFour));
        sheet.setColumnWidth(6, 20 * 256);
        //第一行第7列
        cell1 = row1.createCell(7);
        cell1.setCellStyle(cellStyleTitle);
        cell1.setCellValue(new HSSFRichTextString(creator));
        sheet.setColumnWidth(7, 20 * 256);
        //第一行第7列
        //第一行第7列
        cell1 = row1.createCell(8);
        cell1.setCellStyle(cellStyleTitle);
        cell1.setCellValue(new HSSFRichTextString(createdTime));
        sheet.setColumnWidth(8, 20 * 256);
        for (int i = 1; i <= 500; i++) {
            HSSFRow dataRow = sheet.createRow(i);
            if (!StringUtils.isEmpty(iHgQueryDto.getDepartment())){
                if (i<3){
                    dataRow.createCell(1).setCellValue(iHgQueryDto.getDepartment());
                    dataRow.createCell(2).setCellValue(iHgQueryDto.getProjectName());
                }
            }
            HSSFCell dateCell = dataRow.createCell(8);
            dateCell.setCellStyle(dateCellStyle);

        }

        String fileName = worksheetTitle;
        try {
            fileName = new String(fileName.getBytes("utf-8"), "ISO8859-1");
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        }
        response.reset();
        response.setHeader("Content-Disposition", "attachment;fileName=" + fileName + ".xls");// 指定下载的文件名
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        try {
            OutputStream output = response.getOutputStream();
            wb.write(output);
            output.flush();
            output.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }


    @GetMapping("/downSocialDataTemplate")
    public void downSocialDataTemplate(HttpServletResponse response,IHgQueryDto iHgQueryDto) {
        HSSFWorkbook wb = new HSSFWorkbook();
        String worksheetTitle = "县市区社会服务数据导入模板表";
        // 创建单元格样式
        HSSFCellStyle cellStyleTitle = wb.createCellStyle();
        // 指定单元格居中对齐
        cellStyleTitle.setAlignment(HorizontalAlignment.CENTER);
        // 指定单元格垂直居中对齐
        cellStyleTitle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 指定当单元格内容显示不下时不换行
        cellStyleTitle.setWrapText(false);
        // ------------------------------------------------------------------
        HSSFCellStyle cellStyle = wb.createCellStyle();
        // 指定单元格居中对齐
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        // 指定单元格垂直居中对齐
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 指定当单元格内容显示不下时换行
        cellStyle.setWrapText(true);



        // ------------------------------------------------------------------
        // 创建日期格式样式
        HSSFCellStyle dateCellStyle = wb.createCellStyle();
        dateCellStyle.cloneStyleFrom(cellStyle);
        short dateFormat = wb.createDataFormat().getFormat("yyyy年MM月dd日");
        dateCellStyle.setDataFormat(dateFormat);
        // ------------------------------------------------------------------
        // 设置单元格字体
        HSSFFont font = wb.createFont();
        font.setBold(true);
        font.setFontName("宋体");
        font.setFontHeight((short) 200);
        cellStyleTitle.setFont(font);

        // 工作表名
        String areaName = "区域名称";
        String department = "部门名称";
        String moduleName = "模块名称";
        String projectName = "服务项目名称";
        String remarkOne = "业务量";
        String remarkTwo = "进驻市场主体数量";
        String remarkThree = "上线服务或产品数";
        String remarkFour = "涉及资金（万元）";
        String creator = "填报人";
        String createdTime = "填报时间";

        HSSFSheet sheet = wb.createSheet();
        ExportExcel exportExcel = new ExportExcel(wb, sheet);
        // 创建报表头部
        //exportExcel.createNormalHead(worksheetTitle, 11);
        // 定义第一行
        HSSFRow row1 = sheet.createRow(0);
        //第一行第1列
        HSSFCell cell1 = row1.createCell(0);
        cell1.setCellStyle(cellStyleTitle);
        cell1.setCellValue(new HSSFRichTextString(areaName));
        sheet.setColumnWidth(0, 20 * 256);;
        //第一行第2列
        cell1 = row1.createCell(1);
        cell1.setCellStyle(cellStyleTitle);
        cell1.setCellValue(new HSSFRichTextString(department));
        sheet.setColumnWidth(1, 20 * 256);
        //第一行第2列
        cell1 = row1.createCell(2);
        cell1.setCellStyle(cellStyleTitle);
        cell1.setCellValue(new HSSFRichTextString(moduleName));
        sheet.setColumnWidth(2, 20 * 256);
        //第一行第3列
        cell1 = row1.createCell(3);
        cell1.setCellStyle(cellStyleTitle);
        cell1.setCellValue(new HSSFRichTextString(projectName));
        sheet.setColumnWidth(3, 20 * 256);

        //第一行第5列
        cell1 = row1.createCell(4);
        cell1.setCellStyle(cellStyleTitle);
        cell1.setCellValue(new HSSFRichTextString(remarkOne));
        sheet.setColumnWidth(4, 20 * 256);
        //第一行第6列
        cell1 = row1.createCell(5);
        cell1.setCellStyle(cellStyleTitle);
        cell1.setCellValue(new HSSFRichTextString(remarkTwo));
        sheet.setColumnWidth(5, 20 * 256);
        //第一行第7列
        cell1 = row1.createCell(6);
        cell1.setCellStyle(cellStyleTitle);
        cell1.setCellValue(new HSSFRichTextString(remarkThree));
        sheet.setColumnWidth(6, 20 * 256);
        //第一行第7列
        cell1 = row1.createCell(7);
        cell1.setCellStyle(cellStyleTitle);
        cell1.setCellValue(new HSSFRichTextString(remarkFour));
        sheet.setColumnWidth(7, 20 * 256);
        //第一行第7列
        cell1 = row1.createCell(8);
        cell1.setCellStyle(cellStyleTitle);
        cell1.setCellValue(new HSSFRichTextString(creator));
        sheet.setColumnWidth(8, 20 * 256);
        //第一行第7列
        //第一行第7列
        cell1 = row1.createCell(9);
        cell1.setCellStyle(cellStyleTitle);
        cell1.setCellValue(new HSSFRichTextString(createdTime));
        sheet.setColumnWidth(9, 20 * 256);
        for (int i = 1; i <= 500; i++) {
            HSSFRow dataRow = sheet.createRow(i);
            if (!StringUtils.isEmpty(iHgQueryDto.getDepartment())){
                if (i<3){
                    dataRow.createCell(1).setCellValue(iHgQueryDto.getDepartment());
                    dataRow.createCell(2).setCellValue(iHgQueryDto.getModuleName());
                    dataRow.createCell(3).setCellValue(iHgQueryDto.getProjectName());
                }
            }
            HSSFCell dateCell = dataRow.createCell(9);
            dateCell.setCellStyle(dateCellStyle);
        }
        String fileName = worksheetTitle;
        try {
            fileName = new String(fileName.getBytes("utf-8"), "ISO8859-1");
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        }
        response.reset();
        response.setHeader("Content-Disposition", "attachment;fileName=" + fileName + ".xls");// 指定下载的文件名
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        try {
            OutputStream output = response.getOutputStream();
            wb.write(output);
            output.flush();
            output.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }



    /**
     * 导入政府服务数据
     */
    @Authorization
    @RequestMapping(value = "/importGovernmentData",method = RequestMethod.POST)
    public Wrapper importGovernmentData (@RequestParam("file") MultipartFile file,@RequestParam("userName") String userName) {
        return ihgDataProcessService.importGovernmentData(file,userName);
    }

    /**
     * 导入社会服务数据
     */
    @Authorization
    @RequestMapping(value = "/importSocialData",method = RequestMethod.POST)
    public Wrapper importSocialData (@RequestParam("file") MultipartFile file,@RequestParam("userName") String userName) {
        return ihgDataProcessService.importSocialData(file,userName);
    }





    @GetMapping("/downStaticData")
    public void downStaticData(HttpServletResponse response, iHgDown ihgDown) {
        HSSFWorkbook wb = new HSSFWorkbook();
        String worksheetTitle = "区县数据统计";
        // 创建单元格样式
        HSSFCellStyle cellStyleTitle = wb.createCellStyle();
        // 指定单元格居中对齐
        cellStyleTitle.setAlignment(HorizontalAlignment.CENTER);
        // 指定单元格垂直居中对齐
        cellStyleTitle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 指定当单元格内容显示不下时不换行
        cellStyleTitle.setWrapText(false);
        // ------------------------------------------------------------------
        HSSFCellStyle cellStyle = wb.createCellStyle();
        // 指定单元格居中对齐
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        // 指定单元格垂直居中对齐
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 指定当单元格内容显示不下时换行
        cellStyle.setWrapText(true);



        // ------------------------------------------------------------------
        // 创建日期格式样式
        HSSFCellStyle dateCellStyle = wb.createCellStyle();
        dateCellStyle.cloneStyleFrom(cellStyle);
        short dateFormat = wb.createDataFormat().getFormat("yyyy年MM月dd日");
        dateCellStyle.setDataFormat(dateFormat);
        // ------------------------------------------------------------------
        // 设置单元格字体
        HSSFFont font = wb.createFont();
        font.setBold(true);
        font.setFontName("宋体");
        font.setFontHeight((short) 200);
        cellStyleTitle.setFont(font);

        // 工作表名
        String areaName = "区划";
        String label1 = "政府服务";
        String label2 = "社会服务";
        String name1 = "实际办件总量";
        String name2 = "i黄冈上办理总量";
        String name3= "i黄冈线上办理率";
        String name4 = "业务量";
        String name5 = "进驻市场主体数量";
        String name6 = "上线服务或产品数";
        String name7 = "涉及资金（万元）";


        HSSFSheet sheet = wb.createSheet();
        ExportExcel exportExcel = new ExportExcel(wb, sheet);
        // 创建报表头部
        //exportExcel.createNormalHead(worksheetTitle, 11);
        // 定义第一行
        HSSFRow row1 = sheet.createRow(0);
        //第一行第1列
        HSSFCell cell1 = row1.createCell(0);
        cell1.setCellStyle(cellStyleTitle);
        cell1.setCellValue(new HSSFRichTextString(areaName));
        sheet.setColumnWidth(0, 20 * 256);;

        cell1 = row1.createCell(1);
        cell1.setCellStyle(cellStyleTitle);
        cell1.setCellValue(new HSSFRichTextString(label1));
        sheet.setColumnWidth(1, 25 * 256);
        cell1 = row1.createCell(1);
        cell1.setCellStyle(cellStyleTitle);
        sheet.setColumnWidth(2, 25 * 256);
        cell1 = row1.createCell(1);
        cell1.setCellStyle(cellStyleTitle);
        sheet.setColumnWidth(3, 25 * 256);
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 1, 2));

        cell1 = row1.createCell(4);
        cell1.setCellStyle(cellStyleTitle);
        cell1.setCellValue(new HSSFRichTextString(label2));
        sheet.setColumnWidth(4, 25 * 256);
        cell1 = row1.createCell(5);
        cell1.setCellStyle(cellStyleTitle);
        sheet.setColumnWidth(5, 25 * 256);
        cell1 = row1.createCell(6);
        cell1.setCellStyle(cellStyleTitle);
        sheet.setColumnWidth(6, 25 * 256);
        cell1 = row1.createCell(7);
        cell1.setCellStyle(cellStyleTitle);
        sheet.setColumnWidth(7, 25 * 256);
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 4, 7));
        row1 = sheet.createRow(1);
        cell1 = row1.createCell(0);
        cell1.setCellStyle(cellStyleTitle);
        sheet.setColumnWidth(0, 20 * 256);
        //合并单元格
        sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, 0));

        //第一行第2列
        cell1 = row1.createCell(1);
        cell1.setCellStyle(cellStyleTitle);
        cell1.setCellValue(new HSSFRichTextString(name1));
        sheet.setColumnWidth(1, 25 * 256);
        //第一行第2列
        cell1 = row1.createCell(2);
        cell1.setCellStyle(cellStyleTitle);
        cell1.setCellValue(new HSSFRichTextString(name2));
        sheet.setColumnWidth(2, 25 * 256);
        //第一行第3列
        cell1 = row1.createCell(3);
        cell1.setCellStyle(cellStyleTitle);
        cell1.setCellValue(new HSSFRichTextString(name3));
        sheet.setColumnWidth(3, 25 * 256);

        //第一行第5列
        cell1 = row1.createCell(4);
        cell1.setCellStyle(cellStyleTitle);
        cell1.setCellValue(new HSSFRichTextString(name4));
        sheet.setColumnWidth(4, 25 * 256);
        //第一行第6列
        cell1 = row1.createCell(5);
        cell1.setCellStyle(cellStyleTitle);
        cell1.setCellValue(new HSSFRichTextString(name5));
        sheet.setColumnWidth(5, 25 * 256);
        //第一行第7列
        cell1 = row1.createCell(6);
        cell1.setCellStyle(cellStyleTitle);
        cell1.setCellValue(new HSSFRichTextString(name6));
        sheet.setColumnWidth(6, 25 * 256);
        //第一行第7列
        cell1 = row1.createCell(7);
        cell1.setCellStyle(cellStyleTitle);
        cell1.setCellValue(new HSSFRichTextString(name7));
        sheet.setColumnWidth(7, 25 * 256);

        // iHgDown
        JSONObject jsonObject = new JSONObject();
        jsonObject.put("isGrowth",ihgDown.getIsGrowth());
        if (StringUtils.isNotEmpty(ihgDown.getStartTime())){
            jsonObject.put("startTime", cn.hutool.core.date.DateUtil.parse(ihgDown.getStartTime(), DatePattern.NORM_DATETIME_PATTERN));
            jsonObject.put("endTime", cn.hutool.core.date.DateUtil.parse(ihgDown.getEndTime(), DatePattern.NORM_DATETIME_PATTERN));
        }
        Wrapper warp = ihgDataProcessService.queryStatistical(jsonObject);
        System.out.println(warp);
        List<JSONObject> list = (List<JSONObject>) warp.getResult();
        for (int i = 0; i < list.size(); i++) {
            JSONObject item = list.get(i);
            HSSFRow dataRow = sheet.createRow(i+2);
            dataRow.createCell(0).setCellValue(item.getString("areaName"));
            dataRow.createCell(1).setCellValue(item.getString("governmentAllOrderCount"));
            dataRow.createCell(2).setCellValue(item.getString("governmentIhgOrderCount"));
            dataRow.createCell(3).setCellValue(item.getString("governmentOnlinePercent"));
            dataRow.createCell(4).setCellValue(item.getString("socialOrderCountSum"));
            dataRow.createCell(5).setCellValue(item.getString("socialEnterMarketCountSum"));
            dataRow.createCell(6).setCellValue(item.getString("socialOnlineServiceCountSum"));
            dataRow.createCell(7).setCellValue(item.getString("socialInvolveMoneySum"));
        }
        String fileName = worksheetTitle;
        try {
            fileName = new String(fileName.getBytes("utf-8"), "ISO8859-1");
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        }
        response.reset();
        response.setHeader("Content-Disposition", "attachment;fileName=" + fileName + ".xls");// 指定下载的文件名
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        try {
            OutputStream output = response.getOutputStream();
            wb.write(output);
            output.flush();
            output.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }


    @GetMapping("/downSocialStaticData")
    public void downSocialStaticData(HttpServletResponse response, iHgDown ihgDown) {
        HSSFWorkbook wb = new HSSFWorkbook();
        String worksheetTitle = "社会服务办理质效情况统计";
        // 创建单元格样式
        HSSFCellStyle cellStyleTitle = wb.createCellStyle();
        // 指定单元格居中对齐
        cellStyleTitle.setAlignment(HorizontalAlignment.CENTER);
        // 指定单元格垂直居中对齐
        cellStyleTitle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 指定当单元格内容显示不下时不换行
        cellStyleTitle.setWrapText(false);
        // ------------------------------------------------------------------
        HSSFCellStyle cellStyle = wb.createCellStyle();
        // 指定单元格居中对齐
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        // 指定单元格垂直居中对齐
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 指定当单元格内容显示不下时换行
        cellStyle.setWrapText(true);



        // ------------------------------------------------------------------
        // 创建日期格式样式
        HSSFCellStyle dateCellStyle = wb.createCellStyle();
        dateCellStyle.cloneStyleFrom(cellStyle);
        short dateFormat = wb.createDataFormat().getFormat("yyyy年MM月dd日");
        dateCellStyle.setDataFormat(dateFormat);
        // ------------------------------------------------------------------
        // 设置单元格字体
        HSSFFont font = wb.createFont();
        font.setBold(true);
        font.setFontName("宋体");
        font.setFontHeight((short) 200);
        cellStyleTitle.setFont(font);

        String name4 = "业务量";
        String name5 = "进驻市场主体数量";
        String name6 = "上线服务或产品数";
        String name7 = "涉及资金（万元）";


        HSSFSheet sheet = wb.createSheet();
        ExportExcel exportExcel = new ExportExcel(wb, sheet);
        // 创建报表头部
        //exportExcel.createNormalHead(worksheetTitle, 11);
        // 定义第一行
        HSSFRow row1 = sheet.createRow(0);
        //第一行第1列
        HSSFCell cell1 = row1.createCell(0);
        cell1.setCellStyle(cellStyleTitle);
        cell1.setCellValue(new HSSFRichTextString("部门"));
        sheet.setColumnWidth(0, 20 * 256);;

        cell1 = row1.createCell(1);
        cell1.setCellStyle(cellStyleTitle);
        cell1.setCellValue(new HSSFRichTextString("模块"));
        sheet.setColumnWidth(1, 20 * 256);;

        cell1 = row1.createCell(2);
        cell1.setCellStyle(cellStyleTitle);
        cell1.setCellValue(new HSSFRichTextString("服务项目"));
        sheet.setColumnWidth(2, 20 * 256);;


        cell1 = row1.createCell(3);
        cell1.setCellStyle(cellStyleTitle);
        cell1.setCellValue(new HSSFRichTextString("社会服务"));
        sheet.setColumnWidth(3, 25 * 256);

        cell1 = row1.createCell(4);
        cell1.setCellStyle(cellStyleTitle);
        sheet.setColumnWidth(4, 25 * 256);
        cell1 = row1.createCell(5);
        cell1.setCellStyle(cellStyleTitle);
        sheet.setColumnWidth(5, 25 * 256);

        cell1 = row1.createCell(6);
        cell1.setCellStyle(cellStyleTitle);
        sheet.setColumnWidth(6, 25 * 256);
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 3, 7));


        row1 = sheet.createRow(1);
        cell1 = row1.createCell(0);
        cell1.setCellStyle(cellStyleTitle);
        sheet.setColumnWidth(0, 20 * 256);
        //合并单元格
        sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, 0));

        cell1 = row1.createCell(1);
        cell1.setCellStyle(cellStyleTitle);
        sheet.setColumnWidth(1, 20 * 256);
        //合并单元格
        sheet.addMergedRegion(new CellRangeAddress(0, 1, 1, 1));

        cell1 = row1.createCell(2);
        cell1.setCellStyle(cellStyleTitle);
        sheet.setColumnWidth(2, 20 * 256);
        //合并单元格
        sheet.addMergedRegion(new CellRangeAddress(0, 1, 2, 2));

        //第一行第5列
        cell1 = row1.createCell(3);
        cell1.setCellStyle(cellStyleTitle);
        cell1.setCellValue(new HSSFRichTextString(name4));
        sheet.setColumnWidth(3, 25 * 256);
        //第一行第6列
        cell1 = row1.createCell(4);
        cell1.setCellStyle(cellStyleTitle);
        cell1.setCellValue(new HSSFRichTextString(name5));
        sheet.setColumnWidth(4, 25 * 256);
        //第一行第7列
        cell1 = row1.createCell(5);
        cell1.setCellStyle(cellStyleTitle);
        cell1.setCellValue(new HSSFRichTextString(name6));
        sheet.setColumnWidth(5, 25 * 256);
        //第一行第7列
        cell1 = row1.createCell(6);
        cell1.setCellStyle(cellStyleTitle);
        cell1.setCellValue(new HSSFRichTextString(name7));
        sheet.setColumnWidth(6, 25 * 256);

        // iHgDown
        JSONObject jsonObject = new JSONObject();
        jsonObject.put("isGrowth",ihgDown.getIsGrowth());


        if (StringUtils.isNotEmpty(ihgDown.getStartTime())){
            jsonObject.put("startTime", cn.hutool.core.date.DateUtil.parse(ihgDown.getStartTime(), DatePattern.NORM_DATETIME_PATTERN));
            jsonObject.put("endTime", cn.hutool.core.date.DateUtil.parse(ihgDown.getEndTime(), DatePattern.NORM_DATETIME_PATTERN));
        }

        Wrapper warp = ihgDataProcessService.querySocialStatistical(jsonObject);
        System.out.println(warp);
        List<JSONObject> list = (List<JSONObject>) warp.getResult();
        for (int i = 0; i < list.size(); i++) {
            JSONObject item = list.get(i);
            HSSFRow dataRow = sheet.createRow(i+2);
            dataRow.createCell(0).setCellValue(item.getString("departmentName"));
            dataRow.createCell(1).setCellValue(item.getString("moduleName"));
            dataRow.createCell(2).setCellValue(item.getString("projectName"));
            dataRow.createCell(3).setCellValue(item.getString("socialOrderCountSum"));
            dataRow.createCell(4).setCellValue(item.getString("socialEnterMarketCountSum"));
            dataRow.createCell(5).setCellValue(item.getString("socialOnlineServiceCountSum"));
            dataRow.createCell(6).setCellValue(item.getString("socialInvolveMoneySum"));
        }
        String fileName = worksheetTitle;
        try {
            fileName = new String(fileName.getBytes("utf-8"), "ISO8859-1");
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        }
        response.reset();
        response.setHeader("Content-Disposition", "attachment;fileName=" + fileName + ".xls");// 指定下载的文件名
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        try {
            OutputStream output = response.getOutputStream();
            wb.write(output);
            output.flush();
            output.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    //queryGovernmentStatistical
    @GetMapping("/downGovernmentStaticData")
    public void downGovernmentStaticData(HttpServletResponse response, iHgDown ihgDown) {
        HSSFWorkbook wb = new HSSFWorkbook();
        String worksheetTitle = "政府服务办理质效情况统计";
        // 创建单元格样式
        HSSFCellStyle cellStyleTitle = wb.createCellStyle();
        // 指定单元格居中对齐
        cellStyleTitle.setAlignment(HorizontalAlignment.CENTER);
        // 指定单元格垂直居中对齐
        cellStyleTitle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 指定当单元格内容显示不下时不换行
        cellStyleTitle.setWrapText(false);
        // ------------------------------------------------------------------
        HSSFCellStyle cellStyle = wb.createCellStyle();
        // 指定单元格居中对齐
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        // 指定单元格垂直居中对齐
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 指定当单元格内容显示不下时换行
        cellStyle.setWrapText(true);



        // ------------------------------------------------------------------
        // 创建日期格式样式
        HSSFCellStyle dateCellStyle = wb.createCellStyle();
        dateCellStyle.cloneStyleFrom(cellStyle);
        short dateFormat = wb.createDataFormat().getFormat("yyyy年MM月dd日");
        dateCellStyle.setDataFormat(dateFormat);
        // ------------------------------------------------------------------
        // 设置单元格字体
        HSSFFont font = wb.createFont();
        font.setBold(true);
        font.setFontName("宋体");
        font.setFontHeight((short) 200);
        cellStyleTitle.setFont(font);

        String name1 = "实际办件总量";
        String name2 = "i黄冈上办理总量";
        String name3= "i黄冈线上办理率";


        HSSFSheet sheet = wb.createSheet();
        // 定义第一行
        HSSFRow row1 = sheet.createRow(0);
        //第一行第1列
        HSSFCell cell1 = row1.createCell(0);
        cell1.setCellStyle(cellStyleTitle);
        cell1.setCellValue(new HSSFRichTextString("部门"));
        sheet.setColumnWidth(0, 20 * 256);;

        cell1 = row1.createCell(1);
        cell1.setCellStyle(cellStyleTitle);
        cell1.setCellValue(new HSSFRichTextString("服务项目"));
        sheet.setColumnWidth(1, 20 * 256);;


        cell1 = row1.createCell(2);
        cell1.setCellStyle(cellStyleTitle);
        cell1.setCellValue(new HSSFRichTextString("政府服务"));
        sheet.setColumnWidth(2, 25 * 256);

        cell1 = row1.createCell(3);
        cell1.setCellStyle(cellStyleTitle);
        sheet.setColumnWidth(3, 25 * 256);
        cell1 = row1.createCell(4);
        cell1.setCellStyle(cellStyleTitle);
        sheet.setColumnWidth(4, 25 * 256);

        sheet.addMergedRegion(new CellRangeAddress(0, 0, 2, 4));


        row1 = sheet.createRow(1);
        cell1 = row1.createCell(0);
        cell1.setCellStyle(cellStyleTitle);
        sheet.setColumnWidth(0, 20 * 256);
        //合并单元格
        sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, 0));

        cell1 = row1.createCell(1);
        cell1.setCellStyle(cellStyleTitle);
        sheet.setColumnWidth(1, 20 * 256);
        //合并单元格
        sheet.addMergedRegion(new CellRangeAddress(0, 1, 1, 1));


        //第一行第5列
        cell1 = row1.createCell(2);
        cell1.setCellStyle(cellStyleTitle);
        cell1.setCellValue(new HSSFRichTextString(name1));
        sheet.setColumnWidth(2, 25 * 256);
        //第一行第6列
        cell1 = row1.createCell(3);
        cell1.setCellStyle(cellStyleTitle);
        cell1.setCellValue(new HSSFRichTextString(name2));
        sheet.setColumnWidth(3, 25 * 256);
        //第一行第7列
        cell1 = row1.createCell(4);
        cell1.setCellStyle(cellStyleTitle);
        cell1.setCellValue(new HSSFRichTextString(name3));
        sheet.setColumnWidth(4, 25 * 256);

        // iHgDown
        JSONObject jsonObject = new JSONObject();
        jsonObject.put("isGrowth",ihgDown.getIsGrowth());
        if (StringUtils.isNotEmpty(ihgDown.getStartTime())){
            jsonObject.put("startTime", cn.hutool.core.date.DateUtil.parse(ihgDown.getStartTime(), DatePattern.NORM_DATETIME_PATTERN));
            jsonObject.put("endTime", cn.hutool.core.date.DateUtil.parse(ihgDown.getEndTime(), DatePattern.NORM_DATETIME_PATTERN));
        }
        Wrapper warp = ihgDataProcessService.queryGovernmentStatistical(jsonObject);
        System.out.println(warp);
        List<JSONObject> list = (List<JSONObject>) warp.getResult();
        for (int i = 0; i < list.size(); i++) {
            JSONObject item = list.get(i);
            HSSFRow dataRow = sheet.createRow(i+2);
            dataRow.createCell(0).setCellValue(item.getString("departmentName"));
            dataRow.createCell(1).setCellValue(item.getString("projectName"));
            dataRow.createCell(2).setCellValue(item.getString("governmentAllOrderCount"));
            dataRow.createCell(3).setCellValue(item.getString("governmentIhgOrderCount"));
            dataRow.createCell(4).setCellValue(item.getString("governmentOnlinePercent"));
        }
        String fileName = worksheetTitle;
        try {
            fileName = new String(fileName.getBytes("utf-8"), "ISO8859-1");
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        }
        response.reset();
        response.setHeader("Content-Disposition", "attachment;fileName=" + fileName + ".xls");// 指定下载的文件名
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        try {
            OutputStream output = response.getOutputStream();
            wb.write(output);
            output.flush();
            output.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }


}
